Stored Procedure

Executing stored procedures to perform complex data manipulation tasks in a structured, efficient, and reusable manner.

---

🧩 Overview

A Stored Procedure is a precompiled collection of SQL statements stored within the database. It allows for the execution of complex operations such as insertions, updates, calculations, or validations with optimal performance and control.

---

🎯 Use Cases

• Encapsulate business logic at the database level
• Execute batch processes (e.g., monthly billing, report generation)
• Implement data integrity rules (e.g., audit logging, trigger-like workflows)
• Reduce application-database round-trips for performance

---

⚙️ Structure of a Stored Procedure

Basic Syntax (SQL Server / MySQL):

CREATE PROCEDURE procedure_name (
    IN input_param INT,
    OUT output_param VARCHAR(50)
)
BEGIN
    -- SQL Statements
    SELECT column_name INTO output_param FROM table WHERE id = input_param;
END;

Example: Insert with Validation

CREATE PROCEDURE AddNewPatient (
    IN p_name VARCHAR(100),
    IN p_mobile VARCHAR(15),
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE existing INT;

    SELECT COUNT(*) INTO existing FROM patients WHERE mobile = p_mobile;

    IF existing = 0 THEN
        INSERT INTO patients (name, mobile) VALUES (p_name, p_mobile);
        SET p_result = 'Patient added successfully';
    ELSE
        SET p_result = 'Duplicate mobile number';
    END IF;
END;

---

🔁 Input/Output Parameters

• IN: Accepts values from the caller
• OUT: Returns values to the caller
• INOUT: Accepts and returns values

---

🔒 Benefits

• 🚀 Performance: Reduces SQL compilation time as it's precompiled
• 🔁 Reusability: Can be invoked from multiple apps or modules
• 🔐 Security: Restrict direct table access by encapsulating logic
• 🧱 Modularity: Isolates logic for better maintainability

---

🔗 Execution (via Application)

From SQL Client:

CALL AddNewPatient('John Doe', '9876543210', @msg);
SELECT @msg;

From Application Code (Node.js Example):

const [rows] = await db.execute(
  "CALL AddNewPatient(?, ?, @msg); SELECT @msg;",
  ["John Doe", "9876543210"]
);
console.log(rows[1]); // Result from @msg

---

📓 Logging and Error Handling

Add error handling to stored procedures:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    SET p_result = 'SQL Error occurred';
END;

Optional: Log into a system log table for audit and traceability.

---

📚 Best Practices

• Use meaningful names for procedures and parameters
• Avoid business logic complexity that’s hard to debug
• Keep them short and modular
• Document each procedure’s purpose and input/output clearly
• Use transactions when multiple operations must succeed or fail as one

---

🔬 Testing Procedures

• Use mock tables and test inputs
• Validate all edge cases (e.g., nulls, duplicates, invalid data types)
• Confirm rollback behavior in transaction scopes

---

✅ Success Criteria

Criteria	Condition
Procedure executes cleanly	No runtime errors or SQL exceptions
Output is accurate	Valid return data / status
Reusable logic	Works for a range of input parameters
Secure access	Only allowed users can execute

---

🧩 Related Concepts

• Triggers: Automatic execution on data change (less flexible)
• Functions: Return a value but cannot modify data
• Views: Represent read-only or computed results

---

🔚 Summary

Stored Procedures are vital tools in backend systems where performance, logic encapsulation, and security are crucial. They should be used strategically to balance control, reusability, and maintainability.

---